DECLARE @MONTH NVARCHAR(100)=(SELECT [MAX_MONTH] FROM [DATE_EM_REPORT])
DECLARE @STARTOFMONTH DATETIME= (DATEADD(M,DATEDIFF(MONTH, 0, GETDATE()),0))
DECLARE @DATELASTYEAR nvarchar(8)= CONVERT(NVARCHAR(8),(DATEADD(M,-11,@STARTOFMONTH)),112)
declare @date_update nvarchar(100)= (select [DATE_UPDATE] from [DATE_EM_REPORT])
--print(@STARTOFMONTH)

if((select max(ISSUE_DATE) from SP_DEPOSIT_DPTB_RAW) < @date_update)
begin
insert SP_DEPOSIT_DPTB_RAW
SELECT *,CONVERT(NVARCHAR(6),ISSUE_DATE,112) AS YEARMONTH 
 FROM [SERVER74].[BICDATA].[DBO].[DPTB_MASTER] WHERE ISSUE_DATE > (select max(ISSUE_DATE) from SP_DEPOSIT_DPTB_RAW) and ISSUE_DATE <=@date_update AND APP='DEPOSIT'
 end

if((select max(yearmonth) from SP_DEPOSIT_BEM_RAW) < @MONTH)
begin
insert  SP_DEPOSIT_BEM_RAW 
select * from [SERVER74].[BICDATA].[DBO].[BALANCE_ENDMONTH] WHERE YEARMONTH > (select max(yearmonth) from SP_DEPOSIT_BEM_RAW) AND APP='DEPOSIT'
 end

delete from SP_DEPOSIT_CUSTOMER_DPTB_RAW

insert SP_DEPOSIT_CUSTOMER_DPTB_RAW
SELECT *, CASE WHEN PRODUCT_NAME = '3. OTHERS' THEN '3. Other deposit'
				WHEN PRODUCT_NAME = '2. Term Deposit' THEN '2. Term Deposit'
				WHEN PRODUCT_NAME = '3. OTHER DEPOSIT' THEN '3. Other deposit'
				WHEN PRODUCT_NAME = '3. Others' THEN '3. Other deposit'
				WHEN PRODUCT_NAME = '1. Current Account' THEN '1. Current Account'
				 ELSE PRODUCT_NAME END AS PRODUCT_NAME_QD
FROM SP_DEPOSIT_DPTB_RAW where cif in (select cif from TBL_CUSTOMER)

--select distinct product_name from SP_DEPOSIT_BEM_RAW

delete from sp_deposit_customer_raw

insert sp_deposit_customer_raw
select *, CASE WHEN PRODUCT_NAME = '3. OTHERS' THEN '3. Other deposit'
				WHEN PRODUCT_NAME = '2. Term Deposit' THEN '2. Term Deposit'
				WHEN PRODUCT_NAME = '3. OTHER DEPOSIT' THEN '3. Other deposit'
				WHEN PRODUCT_NAME = '3. Others' THEN '3. Other deposit'
				WHEN PRODUCT_NAME = '1. Current Account' THEN '1. Current Account'
				 ELSE PRODUCT_NAME END AS PRODUCT_NAME_QD
from SP_DEPOSIT_BEM_RAW where cif in (select cif from TBL_CUSTOMER)

select YEARMONTH, product_name_qd,dao, count(ACCTNO) as quantity, sum(BAL_QD) as balance into #tam
 from sp_deposit_customer_raw where dao is not null and dao in (select dao from TBL_HR)
 group by YEARMONTH, product_name_qd, dao
 order by YEARMONTH, dao
 
select YEARMONTH, product_name_qd,dao, count(ACCTNO) as quantity, sum(BAL_QD) as balance into #new_deposit_raw
 from SP_DEPOSIT_CUSTOMER_DPTB_RAW where dao is not null and dao in (select dao from TBL_HR)
 group by YEARMONTH, product_name_qd, dao
 order by YEARMONTH, dao

delete from TBL_SP_DEPOSIT_EOP
 
DECLARE @MONTH1 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -1, GETDATE())),112)
DECLARE @MONTH2 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -2, GETDATE())),112)
DECLARE @MONTH3 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -3, GETDATE())),112)
DECLARE @MONTH4 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -4, GETDATE())),112)
DECLARE @MONTH5 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -5, GETDATE())),112)
DECLARE @MONTH6 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -6, GETDATE())),112)
DECLARE @MONTH7 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -7, GETDATE())),112)
DECLARE @MONTH8 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -8, GETDATE())),112)
DECLARE @MONTH9 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -9, GETDATE())),112)
DECLARE @MONTH10 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -10, GETDATE())),112)
DECLARE @MONTH11 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -11, GETDATE())),112)
DECLARE @MONTH0 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, 0, GETDATE())),112)

exec('
select * into #tam1
from
(select yearmonth, dao,product_name_qd, col, value from #tam
cross apply
( select ''quantity'', cast(quantity as numeric) union all
  select ''balance'', cast(balance as numeric) ) c(col, value)) d
pivot
( max(value)
  for yearmonth in (['+@MONTH1+'],['+@MONTH2+'],['+@MONTH3+'],['+@MONTH4+'],['+@MONTH5+'],['+@MONTH6+'],['+@MONTH7+'],['+@MONTH8+'],['+@MONTH9+'],['+@MONTH10+'],['+@MONTH11+'],['+@MONTH0+'])) piv;

  insert TBL_SP_DEPOSIT_EOP
  select dao, product_name_qd, col,
  ['+@MONTH0+'] as [month0],['+@MONTH1+'] as [month1],['+@MONTH2+'] as [month2],['+@MONTH3+'] as [month3],['+@MONTH4+'] as [month4],['+@MONTH5+'] as [month5],['+@MONTH6+'] as [month6],['+@MONTH7+'] as [month7],['+@MONTH8+'] as [month8],['+@MONTH9+'] as [month9],['+@MONTH10+'] as [month10],['+@MONTH11+'] as [month11],1 as Group_Type  from #tam1

  select * into #tam2
from
(select yearmonth, dao,product_name_qd, col, value from #new_deposit_raw
cross apply
( select ''quantity'', cast(quantity as numeric) union all
  select ''balance'', cast(balance as numeric) ) c(col, value)) d
pivot
( max(value)
  for yearmonth in (['+@MONTH1+'],['+@MONTH2+'],['+@MONTH3+'],['+@MONTH4+'],['+@MONTH5+'],['+@MONTH6+'],['+@MONTH7+'],['+@MONTH8+'],['+@MONTH9+'],['+@MONTH10+'],['+@MONTH11+'],['+@MONTH0+'])) piv;

  insert TBL_SP_DEPOSIT_EOP
  select dao, product_name_qd, col,
  ['+@MONTH0+'] as [month0],['+@MONTH1+'] as [month1],['+@MONTH2+'] as [month2],['+@MONTH3+'] as [month3],['+@MONTH4+'] as [month4],['+@MONTH5+'] as [month5],['+@MONTH6+'] as [month6],['+@MONTH7+'] as [month7],['+@MONTH8+'] as [month8],['+@MONTH9+'] as [month9],['+@MONTH10+'] as [month10],['+@MONTH11+'] as [month11],2 from #tam2
  ')

  declare @count int = 0

  while (@count <12)
  begin 
  exec('
  update TBL_SP_DEPOSIT_EOP set month'+@count+'=0 where MONTH'+@count+' is null

  update TBL_SP_DEPOSIT_EOP set month'+@count+'=0 where col=''balance'' and group_type=2
  ')
  set @count = @count+1
  end

delete from sp_deposit_last_date

insert sp_deposit_last_date
select * 
from SERVER74.BICDATA.DBO.BALANCE_LIVE where BUSINESS_DATE= @DATE_UPDATE AND APP='DEPOSIT'

select *, CASE WHEN PRODUCT_NAME = '3. OTHERS' THEN '3. Other deposit'
				WHEN PRODUCT_NAME = '2. Term Deposit' THEN '2. Term Deposit'
				WHEN PRODUCT_NAME = '3. Others' THEN '3. Other deposit'
				WHEN PRODUCT_NAME = '3. OTHER DEPOSIT' THEN '3. Other deposit'
				WHEN PRODUCT_NAME = '1. Current Account' THEN '1. Current Account'
				 ELSE PRODUCT_NAME END AS PRODUCT_NAME_QD
 into #tam_ngay 
 from sp_deposit_last_date where cif in (select cif from TBL_CUSTOMER)

select product_name_qd,dao, count(ACCTNO) as quantity, sum(BAL_QD) as balance into #sp_deposit_by_day
 from #tam_ngay where dao is not null and dao in (select dao from TBL_HR)
 group by product_name_qd, dao
 order by dao

 update TBL_SP_DEPOSIT_EOP set month0= b.balance
 from TBL_SP_DEPOSIT_EOP as a, #sp_deposit_by_day as b
 where a.dao=b.dao and a.product_name_qd = b.product_name_qd and a.group_type= 1 and col='balance'

-- EXEC('IF EXISTS
--      (SELECT *
--         FROM [SERVER12].[SMECustomer360].DBO.SYSOBJECTS O
--        WHERE O.XTYPE IN (''U'') AND O.NAME = ''TBL_SP_DEPOSIT_EOP'')
--        delete from [SERVER12].[SMECustomer360].dbo.TBL_SP_DEPOSIT_EOP')
 
--insert [SERVER12].[SMECustomer360].dbo.TBL_SP_DEPOSIT_EOP
--select * from TBL_SP_DEPOSIT_EOP